package com.cike.productmanager.DBHelper;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.cike.productmanager.DBHelper.TablesColumns.ActivityColumns;
import com.cike.productmanager.DBHelper.TablesColumns.ActivityFeedbackColumns;
import com.cike.productmanager.DBHelper.TablesColumns.ContractColumns;
import com.cike.productmanager.DBHelper.TablesColumns.ContractFeedbackColumns;
import com.cike.productmanager.DBHelper.TablesColumns.CustomerColumns;
import com.cike.productmanager.DBHelper.TablesColumns.DisplayGuideColumns;
import com.cike.productmanager.DBHelper.TablesColumns.MarketVisitColumns;
import com.cike.productmanager.DBHelper.TablesColumns.PermissionColumns;
import com.cike.productmanager.DBHelper.TablesColumns.RoleColumns;
import com.cike.productmanager.DBHelper.TablesColumns.StoreColumns;
import com.cike.productmanager.DBHelper.TablesColumns.UserColumns;
import com.cike.productmanager.DBHelper.TablesColumns.VisitColumns;

public class DBHelper extends SQLiteOpenHelper{

	private static final String DB_NAME = "mdrp.db";

    private static final int DB_VERSION = 1;
	
	public interface TABLE{
		public static final String USER = "user";
		
		public static final String STORE = "store";
		
		public static final String ACTIVITY = "activity";
		
		public static final String ACTIVITY_FEEDBACK = "activityFeedback";
		
		public static final String CONTRACT = "contract";
		
		public static final String CONTRACT_FEEDBACK = "contractFeedback";
		
		public static final String VISIT = "visit";
		
		public static final String DISPLAY_GUIDE = "displayGuide";
		
		public static final String CUSTOMER = "customer";
		
		public static final String ROLE = "role";
		
		public static final String PERMISSION = "permission";
		
		public static final String MARKET_VISIT = "marketVisit";
	}
	
	private static final String TAG = "ProfileDatabaseHelper";
	
	static DBHelper mDbHelper;
	
	
	private static final String CREATE_USER_TABLE_SQL = 
			"CREATE  TABLE IF NOT EXISTS " + TABLE.USER + "(" +
					  UserColumns.ID + " INT AUTO_INCREMENT ," +
					  UserColumns.USER_ID + " INT NOT NULL ," +
					  UserColumns.USER_NAME + " VARCHAR(32) NULL ," +
					  UserColumns.PASSWORD + " VARCHAR(32) NULL ," +
					  UserColumns.POSITION + " VARCHAR(16) NULL ," +
					  UserColumns.IMEI + " CHAR(15) NULL ," +
					  UserColumns.LAST_VISIT + " TIMESTAMP NULL ," +
					  UserColumns.EMPLOYEE_ID + " VARCHAR(16) NULL ," +
					  UserColumns.EMPLOYEE_NAME + " VARCHAR(32) NULL ," +
					  UserColumns.PHONE + " VARCHAR(16) NULL ," +
					  UserColumns.COUNTRY + " VARCHAR(16) NULL ," +
					  UserColumns.PROVINCE + " VARCHAR(16) NULL ," +
					  UserColumns.CITY + " VARCHAR(16) NULL ," +
					  UserColumns.ADDRESS + " VARCHAR(255) NULL ," +
					  UserColumns.LEAD_ID + " INT NULL ," +
					  UserColumns.REMARK + " VARCHAR(255) NULL ," +
					  UserColumns.AUTH_CODE + " VARCHAR(32) NULL ," +
					  "PRIMARY KEY (" + UserColumns.ID + ") " +
					  ")";
	
	private static final String CREATE_CUSTOMER_TABLE_SQL = 
			"CREATE  TABLE IF NOT EXISTS " + TABLE.CUSTOMER + "(" +
			CustomerColumns.ID + " INT AUTO_INCREMENT ," +
			CustomerColumns.CUSTOMER_ID + " INT NOT NULL ," +
			CustomerColumns.CUSTOMER_NAME + " VARCHAR(32) NULL ," +
			CustomerColumns.HEAD_OFFICE + " VARCHAR(32) NULL ," +
			CustomerColumns.USER_ID + " INT NULL ," +
			"PRIMARY KEY (" + CustomerColumns.CUSTOMER_ID + ") ," +
			"FOREIGN KEY (" + CustomerColumns.USER_ID +" )" +
			"REFERENCES "+ TABLE.CUSTOMER + " (" + CustomerColumns.USER_ID +" )"+
			")";

	
	private static final String CREATE_STORE_TABLE_SQL = 
			"CREATE TABLE IF NOT EXISTS " + TABLE.STORE + "(" +
			 StoreColumns.ID + " INT AUTO_INCREMENT," +
		     StoreColumns.STORE_ID + " INT NOT NULL," +
			 StoreColumns.STORE_NAME + " VARCHAR(64) NULL," +
			 StoreColumns.REGION + " VARCHAR(32) NULL," +
			 StoreColumns.SUBREGION + " VARCHAR(32) NULL," +
			 StoreColumns.PROVINCE + " VARCHAR(16) NULL," +
			 StoreColumns.CITY + " VARCHAR(16) NULL," +
			 StoreColumns.DISTRICT + " VARCHAR(32) NULL," +
			 StoreColumns.STREET + " VARCHAR(64) NULL," +
			 StoreColumns.LONGITUDE + " DOUBLE NULL," +
			 StoreColumns.LATITUDE + " DOUBLE NULL," +
			 StoreColumns.MAP_ID + " INT NULL," +
			 StoreColumns.ZIP_CODE + " CHAR(16) NULL," +
			 StoreColumns.AREA_CODE + " VARCHAR(4) NULL," +//类型有问题
			 StoreColumns.PHONE + " VARCHAR(16) NULL," +
	         StoreColumns.USER_ID + " INT NOT NULL," +
	         StoreColumns.OPEN_TIME + " TIME NULL," +
	         StoreColumns.CLOSE_TIME + " TIME NULL," +
	         StoreColumns.STORE_AREA + " DOUBLE NULL," +
	         StoreColumns.CHECKOUT_NUMBER + " INT NULL," +
	         StoreColumns.CHECKOUT_NUMBER_OFT + " INT NULL," +
	         StoreColumns.STORE_TYPE + " VARCHAR(16) NULL," +
	         StoreColumns.CUSTOMER_ID + " INT NOT NULL," +
	         StoreColumns.PROCU_CENTERS_TYPE + " VARCHAR(32) NULL," +
	         StoreColumns.AUTHENTICATED + " INT NOT NULL," +
	         StoreColumns.TELEPHONE + " VERCHAR(16) NULL," +
	         StoreColumns.REMARK + " VARCHAR(255) NULL," +
	         "PRIMARY KEY (" + StoreColumns.STORE_ID +") ," +
             "FOREIGN KEY (" + StoreColumns.CUSTOMER_ID + " )" +
             "REFERENCES " + TABLE.CUSTOMER + " (" + CustomerColumns.CUSTOMER_ID+" )" +
             ")";
	
	private static final String CREATE_ACTIVITY_TABLE_SQL = 
			"CREATE TABLE IF NOT EXISTS " + TABLE.ACTIVITY + "(" +
			 ActivityColumns.ID + " INT AUTO_INCREMENT," +
			 ActivityColumns.ACTIVITY_ID + " INT NOT NULL," +
			 ActivityColumns.ACTIVITY_NAME + " VARCHAR(64) NULL," +
			 ActivityColumns.START_TIME + " DATETIME NULL," +
			 ActivityColumns.END_TIME + " DATETIME NULL," +
			 ActivityColumns.DISPLAY_GUIDE_ID + " INT NULL," +
			 ActivityColumns.STORE_ID + " INT NULL," +
			 ActivityColumns.SALER_NUMBER + " INT NULL," +
			 ActivityColumns.GOAL + " VARCHAR(255) NULL," +
			 ActivityColumns.REQUIRE + " VARCHAR(255) NULL," +
			 ActivityColumns.TYPE + " VARCHAR(16) NULL," +
			 ActivityColumns.IMPORTANCE + " VARCHAR(16) NULL," +
			 ActivityColumns.REMARK + " VARCHAR(255) NULL," +
			 "PRIMARY KEY (" + ActivityColumns.ACTIVITY_ID + ") ," +
			 "FOREIGN KEY (" + ActivityColumns.STORE_ID + " )" +
			 "REFERENCES " + TABLE.STORE + " (" + StoreColumns.STORE_ID + " )" +
			 ")";
	
	
	public static final String CREATE_ACTIVITY_FEEDBACK_SQL =
			"CREATE TABLE IF NOT EXISTS " + TABLE.ACTIVITY_FEEDBACK + "(" +
	        ActivityFeedbackColumns.ID + " INT AUTO_INCREMENT ," +
	        ActivityFeedbackColumns.ACTIVITY_FEEDBACK_ID + " INT NULL," +
	        ActivityFeedbackColumns.SALER_STATUS + " VARCHAR(255) NOT NULL," +
	        ActivityFeedbackColumns.PRODUCT_STATUS + " VARCHAR(255) NOT NULL," +
	        ActivityFeedbackColumns.DISPLAY_STATUS + " INT NOT NULL," +
	        ActivityFeedbackColumns.MATERIAL_STATUS + " INT NOT NULL," +
	        ActivityFeedbackColumns.STATUS + " VARCHAR(255) NULL," +
	        ActivityFeedbackColumns.LONGITUDE + " DOUBLE NULL," +
	        ActivityFeedbackColumns.LATITUDE + " DOUBLE NULL," +
	        ActivityFeedbackColumns.PHOTOED + " INT NULL," +
	        ActivityFeedbackColumns.ACTIVITY_ID + " INT NOT NULL," +
	        "PRIMARY KEY (" + ActivityFeedbackColumns.ACTIVITY_FEEDBACK_ID + ") ," +
	        "FOREIGN KEY (" + ActivityFeedbackColumns.ACTIVITY_ID+ " )"+
	        "REFERENCES " + TABLE.ACTIVITY + " ("+ActivityColumns.ACTIVITY_ID + " )" +
	        ")";
	
	
	private static final String CREATE_CONTACT_TABLE_SQL = 
			"CREATE TABLE IF NOT EXISTS " + TABLE.CONTRACT + "(" +
			 ContractColumns.ID + " INT AUTO_INCREMENT," +
			 ContractColumns.CONTRACT_ID + " INT NOT NULL,"+
			 ContractColumns.CONTRACT_NAME + " VARCHAR(64) NULL," +
			 ContractColumns.DISPLAY_GUIDE_ID + " INT NULL," +
			 ContractColumns.TYPE + " INT NULL," +
			 ContractColumns.START_TIME + " DATETIME NULL," +
			 ContractColumns.END_TIME + " DATETINE NULL," +
			 ContractColumns.CONTENT + " VARCHAR(255) NULL," +
			 ContractColumns.IMPORTANCE + " INT NULL," +
			 ContractColumns.STORE_ID + " INT NOT NULL," +
			 "PRIMARY KEY (" + ContractColumns.CONTRACT_ID + ") ," +
			 "FOREIGN KEY (" + ContractColumns.STORE_ID+ " )"+
		     "REFERENCES " + TABLE.STORE + " ("+StoreColumns.STORE_ID + " )" +
		     ")";
	
	public static final String CREATE_CONTRACT_FEEDBACK_SQL =
			"CREATE TABLE IF NOT EXISTS " + TABLE.CONTRACT_FEEDBACK + "(" +
	        ContractFeedbackColumns.ID + " INT AUTO_INCREMENT ," +
	        ContractFeedbackColumns.CONTRACT_FEEDBACK_ID + " INT NULL," +
	        ContractFeedbackColumns.STATUS + " VARCHAR(255) NULL," +
	        ContractFeedbackColumns.LONGITUDE + " DOUBLE NULL," +
	        ContractFeedbackColumns.LATITUDE + " DOUBLE NULL," +
	        ContractFeedbackColumns.PHOTOED + " INT NULL," +
	        ContractFeedbackColumns.CONTRACT_ID + " INT NOT NULL," +
	        "PRIMARY KEY (" + ContractFeedbackColumns.CONTRACT_FEEDBACK_ID + ") ," +
	        "FOREIGN KEY (" + ContractFeedbackColumns.CONTRACT_ID+ " )"+
	        "REFERENCES " + TABLE.CONTRACT + " ("+ContractColumns.CONTRACT_ID + " )" +
	        ")";
	
	private static final String CREATE_ROLE_SQL =
			"CREATE TABLE IF NOT EXISTS " + TABLE.ROLE + " (" +
	        RoleColumns.ID + " INT AUTO_INCREMENT," +
	        RoleColumns.ROLE_ID + " INT NOT NULL," +
	        RoleColumns.ROLE_NAME + " VARCHAR(30) NULL," +
	        RoleColumns.PERMISSION_ID + " INT NULL," +
	        RoleColumns.USER_ID + " INT NULL," +
	        "PRIMARY KEY (" + RoleColumns.ROLE_ID + ") " +
	        ")";
	
	public static final String CREATE_PERMISSION_SQL =
			"CREATE TABLE IF NOT EXISTS " + TABLE.PERMISSION + " (" +
			PermissionColumns.ID + " INT AUTO_INCREMNET," +
			PermissionColumns.PERMISSION_ID + " INT NOT NULL," +
			PermissionColumns.PERMISSION_NAME + " VARCHAR(30) NULL," +
			"PRIMARY KEY (" + PermissionColumns.PERMISSION_ID + ") " +
			")";
	
	private static final String CREATE_VISIT_TABLE_SQL = 
			"CREATE TABLE IF NOT EXISTS " + TABLE.VISIT + "(" +
			 VisitColumns.ID+ " INTEGER PRIMARY KEY AUTOINCREMENT," +
			 VisitColumns.TYPE + " VARCHAR(32)," +
			 VisitColumns.TASK_ID + " INT NOT NULL," +
			 VisitColumns.START_TIME + " DATETIME NULL," +
			 VisitColumns.END_TIME + " DATETIME NULL," +
			 VisitColumns.STATE + " VARCHAR(32)" +
			")";
	
	private static final String CREATE_MARKET_VISIT_SQL =
			"CREATE TABLE IF NOT EXISTS " + TABLE.MARKET_VISIT + " (" +
	        MarketVisitColumns.ID + " INT AUTO_INCREMENT," +
	        MarketVisitColumns.MARKET_VISIT_ID + " INT NOT NULL," +
	        MarketVisitColumns.STORE_ID + " INT NOT NULL," +
	        MarketVisitColumns.TYPE + " INT NULL," +
	        MarketVisitColumns.TASK_ID + " INT NULL," +
	        MarketVisitColumns.LOG + " VARCHAR(255) NULL," +
	        MarketVisitColumns.CREATED_TIME + " DATETIME NULL," +
	        MarketVisitColumns.USER_ID + " INT NULL," +
	        "PRIMARY KEY (" + MarketVisitColumns.MARKET_VISIT_ID + ")," +
            "FOREIGN KEY (" + MarketVisitColumns.USER_ID + " )" +
            "REFERENCES " + TABLE.USER+" (" + UserColumns.USER_ID + " )"+
            ")";
	
	
	
	private static final String CREATE_DISPLAY_GUIDE_TABLE_SQL = 
			"CREATE TABLE IF NOT EXISTS " + TABLE.DISPLAY_GUIDE + "(" +
			 DisplayGuideColumns.ID + " INT AUTO_INCREMENT," +
			 DisplayGuideColumns.DISPLAY_GUIDE_ID + " INT NOT NULL," +
			 DisplayGuideColumns.DISPLAY_GUIDE_NAME + " VARCHAR(16) NULL," +
			 DisplayGuideColumns.STORE_TYPE + " VARCHAR(16) NULL," +
			 DisplayGuideColumns.INTRODUCE + " VARCHAR(255)," +
			 DisplayGuideColumns.PROVINCE + " VARCHAR(16) NULL," +
			 DisplayGuideColumns.CITY + " VARCHAR(16) NULL," +
			 DisplayGuideColumns.CUSTOMER_ID + " INT NULL," +
			 DisplayGuideColumns.CREATED_TIME + " DATETIME NULL," +
			 DisplayGuideColumns.DEADLINE + " DATETIME NULL," +
			 DisplayGuideColumns.TYPE + " INT NULL," +
			 DisplayGuideColumns.USER_ID +" INT NULL," +
			 DisplayGuideColumns.CONTENT + " VARCHAR(255) NULL," +
			 "PRIMARY KEY (" + DisplayGuideColumns.DISPLAY_GUIDE_ID + " ) ," +
			 "FOREIGN KEY (" + DisplayGuideColumns.USER_ID + " )" +
			 "REFERENCES " + TABLE.USER +" (" + UserColumns.USER_ID + " )" +
			 ")";
	
	
	private static final String CREATE_ROLE_HAS_USER_TABLE_SQL =
			"CREATE  TABLE IF NOT EXISTS " + "Role_has_User ("+
			"`Role_roleId` INT NOT NULL ,"+
            "`User_userId` INT NOT NULL ,"+
            "PRIMARY KEY (`Role_roleId`, `User_userId`) ,"+
            "FOREIGN KEY (`Role_roleId` )"+
            "REFERENCES " + TABLE.ROLE + " ("+RoleColumns.ROLE_ID+" )"+
            "FOREIGN KEY (`User_userId` )"+
            "REFERENCES " + TABLE.USER + " ("+UserColumns.USER_ID+" )"+
            ")";
	
	private static final String CREATE_PERMISSION_HAS_ROLE =
			"CREATE  TABLE IF NOT EXISTS " + "`Permission_has_Role` ("+
	        "`Permission_id` INT NOT NULL ,"+
			"`Role_roleId` INT NOT NULL ,"+
	        "PRIMARY KEY (`Permission_id`, `Role_roleId`) ,"+
            "FOREIGN KEY (`Permission_id` )"+
            "REFERENCES " + TABLE.PERMISSION + " ("+PermissionColumns.PERMISSION_ID+" )"+
            "FOREIGN KEY (`Role_roleId` )"+
            "REFERENCES " + TABLE.ROLE + " ("+RoleColumns.ROLE_ID+" )"+
            ")";
	
	private static final String CREATE_USER_HAS_STORE =
			"CREATE  TABLE IF NOT EXISTS " + "`User_has_Store` ("+
            "`User_userId` INT NOT NULL ,"+
            "`Store_storeId` INT NOT NULL ,"+
            "PRIMARY KEY (`User_userId`, `Store_storeId`) ,"+
            "FOREIGN KEY (`User_userId` )"+
            "REFERENCES " + TABLE.USER + " ("+UserColumns.USER_ID+" )"+
            "FOREIGN KEY (`Store_storeId` )"+
            "REFERENCES " +  TABLE.STORE + " ("+StoreColumns.STORE_ID+" )"+
            ")";
	
	public DBHelper(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
	}

    public static void creatProfileTables(SQLiteDatabase db){
		db.execSQL(CREATE_ACTIVITY_TABLE_SQL);
		Log.d(TAG, "tables1 has been created");
		db.execSQL(CREATE_CONTACT_TABLE_SQL);
		Log.d(TAG, "tables2 has been created");
		db.execSQL(CREATE_DISPLAY_GUIDE_TABLE_SQL);
		Log.d(TAG, "tables3 has been created");
		db.execSQL(CREATE_STORE_TABLE_SQL);
		Log.d(TAG, "tables4 has been created");
		db.execSQL(CREATE_USER_HAS_STORE);
		Log.d(TAG, "tables5 has been created");
		db.execSQL(CREATE_USER_TABLE_SQL);
		db.execSQL(CREATE_VISIT_TABLE_SQL);
		db.execSQL(CREATE_MARKET_VISIT_SQL);
		db.execSQL(CREATE_ACTIVITY_FEEDBACK_SQL);
		db.execSQL(CREATE_CONTRACT_FEEDBACK_SQL);
		db.execSQL(CREATE_CUSTOMER_TABLE_SQL);
		db.execSQL(CREATE_ROLE_SQL);
		db.execSQL(CREATE_ROLE_HAS_USER_TABLE_SQL);
		db.execSQL(CREATE_PERMISSION_SQL);
		db.execSQL(CREATE_PERMISSION_HAS_ROLE);
		Log.d(TAG, "tables has been created");
	}
    
    public static synchronized DBHelper getInstance(Context context){
    	if(mDbHelper == null){
    		mDbHelper = new DBHelper(context);
    	}
    	return mDbHelper;
    }
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		// 创建数据库
		creatProfileTables(db);
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		//更改数据库版本的操作
		db.execSQL("DROP TABLE IF EXISTS "+ TABLE.ACTIVITY); 
		db.execSQL("DROP TABLE IF EXISTS "+ TABLE.ACTIVITY_FEEDBACK);
		db.execSQL("DROP TABLE IF EXISTS "+ TABLE.CONTRACT);
		db.execSQL("DROP TABLE IF EXISTS "+ TABLE.CONTRACT_FEEDBACK);
		db.execSQL("DROP TABLE IF EXISTS "+ TABLE.CUSTOMER);
		db.execSQL("DROP TABLE IF EXISTS "+ TABLE.DISPLAY_GUIDE);
		db.execSQL("DROP TABLE IF EXISTS "+ TABLE.MARKET_VISIT);
		db.execSQL("DROP TABLE IF EXISTS "+ TABLE.PERMISSION);
		db.execSQL("DROP TABLE IF EXISTS "+ TABLE.ROLE);
		db.execSQL("DROP TABLE IF EXISTS "+ TABLE.STORE);
		db.execSQL("DROP TABLE IF EXISTS "+ TABLE.USER);
		db.execSQL("DROP TABLE IF EXISTS "+ TABLE.VISIT);
		db.execSQL("DROP TABLE IF EXISTS "+ "User_has_Store");
		db.execSQL("DROP TABLE IF EXISTS "+ "Permission_has_Role");
		db.execSQL("DROP TABLE IF EXISTS "+ "Role_has_User");
	}
	
	@Override
	public void onOpen(SQLiteDatabase db) {
		super.onOpen(db);
		Log.i("sqlite", "DataBase is opened!");
	}
	
}
